
import time
from ns import Ns
import sqlite3
from datetime import datetime, timedelta
db_file = 'ns_data.db'

def get_ns_rxframes(host="192.169.42.201:8080"):

    n = Ns(host=host)

    rxframes = n.get_rxframes_list_up()

    return rxframes


def create_table():

    conn = sqlite3.connect(db_file)
    cursor = conn.cursor()

    with open('db.sql', 'r') as file:
        script = file.read()
        cursor.executescript(script)

    conn.commit()
    conn.close()

def convert_to_beijing_time(timestamp):
    # 转换为datetime对象
    dt = datetime.strptime(timestamp, '%Y-%m-%dT%H:%M:%S.%fZ')

    # 增加8小时来获得北京时间
    beijing_time = dt + timedelta(hours=8)

    # 格式化为北京时间字符串
    beijing_time_str = beijing_time.strftime('%Y-%m-%d %H:%M:%S')

    return beijing_time_str

def insert_data(rxframes_data):

    conn = sqlite3.connect(db_file)
    cursor = conn.cursor()

    for dev_info in rxframes_data:
        data = dev_info["data"]
        up_time = convert_to_beijing_time(dev_info["datetime"])
        devaddr = dev_info["devaddr"]
        gateways = dev_info["gateways"][0]["mac"]

        sql = "INSERT OR IGNORE INTO dev_data (up_time, data, devaddr, gateways) VALUES (?, ?, ?, ?);"

        cursor.execute(sql, (up_time, data, devaddr, gateways))

        # print("已插入：{}--{}".format(devaddr, up_time))

        conn.commit()

    cursor.close()
    conn.close()

def run():

    # 调用函数创建表
    create_table()

    host_list = ["192.169.42.201:8080", "192.169.42.202:8080"]
    # host_list = ["192.169.42.201:8080"]

    for host in host_list:

        rxframes = get_ns_rxframes(host=host)

        insert_data(rxframes)


if __name__ == "__main__":

    while True:

        try:
            
            run()

        except Exception as e:

            time.sleep(3600 * 1)

        else:

            time.sleep(3600 * 3)

    

